package co.yixiang.modules.shop.repository;

import co.yixiang.modules.shop.domain.YxInvestOrder;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
* @author frq
* @date 2020-06-10
*/
public interface YxInvestOrderRepository extends JpaRepository<YxInvestOrder, Integer>, JpaSpecificationExecutor<YxInvestOrder> {
    /**
     * 统计业务员提成
     * @return
     */
    @Query(value = "SELECT date_format(add_time, '%Y%m') as month,sales_man_name as salesName,sales_avatar as salesAvatar," +
                   "sales_phone as salesPhone,count(1) as orderCount,sum(price * 5 / 100) as commission FROM " +
                   " yx_invest_order WHERE refund_status = 0 AND paid = 1 AND audit_status = 1 AND " +
                   " PERIOD_DIFF(date_format(now(), '%Y%m'),date_format(add_time, '%Y%m')) = 1 " +
                   " GROUP BY sales_man_name,sales_avatar,sales_phone,date_format(add_time, '%Y%m')",nativeQuery = true)
    List<Map<String,Object>> findSalesCommission();
    /**
     * 获取投资到期订单ID
     * @return
     */
    @Query(value = "select id from yx_invest_order yx where yx.invest_status=3 and now() >yx.end_date",nativeQuery = true)
    List<Integer> findByListOrderId();
    @Modifying
    @Query(value = "update yx_invest_order set invest_status=?2 where id=?1",nativeQuery = true)
    void updateInvestStatus(Integer id, int investStatus);

    /**
     * 查询已认购的商品
     * @param storeName
     * @param beginDate
     * @param endDate
     * @param investName
     * @param investPhone
     * @param pageable
     */
    @Query(nativeQuery = true,value = "SELECT yxo.id as id,yxp.store_name as storeName,yxp.image,yxs.cate_name as cateName,yxp.invest_cost as investCost,yxo.price,yxo.add_Time as addTime," +
            " yxo.invest_name as investName,yxo.invest_phone as investPhone,yxo.invest_status as investStatus,yxo.contract_url as contractUrl,yxo.sale_contract_url as saleContractUrl FROM yx_store_product yxp" +
            " INNER JOIN yx_invest_order_cart_info yxi ON yxp.id = yxi.product_id INNER JOIN yx_invest_order yxo ON yxi.order_id=yxo.id " +
            " LEFT JOIN yx_store_category yxs on yxp.cate_id=yxs.id " +
            " where  if(?1 !='',yxp.store_name LIKE CONCAT('%',?1,'%'),1=1)  and if(?2 !='',yxo.add_Time BETWEEN ?2 and ?3,1=1)" +
            " and if(?4 !='',yxo.invest_name LIKE CONCAT('%',?4,'%'),1=1) and if(?5 !='',yxo.invest_phone LIKE CONCAT('%',?5,'%'),1=1) order by yxo.add_Time DESC ",
           countQuery = "select COUNT(*) FROM yx_store_product yxp  INNER JOIN yx_invest_order_cart_info yxi ON yxp.id = yxi.product_id" +
                   " INNER JOIN yx_invest_order yxo ON yxi.order_id=yxo.id LEFT JOIN yx_store_category yxs on yxp.cate_id=yxs.id" +
                   " where  if(?1 !='',yxp.store_name LIKE CONCAT('%',?1,'%'),1=1)  and if(?2 !='',yxo.add_Time BETWEEN ?2 and ?3,1=1)" +
                   " and if(?4 !='',yxo.invest_name LIKE CONCAT('%',?4,'%'),1=1) and if(?5 !='',yxo.invest_phone LIKE CONCAT('%',?5,'%'),1=1)")
    Page<Map>  findListSubscribeGoods(String storeName, String beginDate, String endDate, String investName, String investPhone, Pageable pageable);
    /**
     * 查询商品ID
     * @param orderId 投资订单ID
     * @return
     */
    @Query(value = "select b.product_id  from yx_invest_order a inner join yx_invest_order_cart_info b on a.id=b.order_id where b.order_id=?1",nativeQuery = true)
    List<Integer> findListProductId(Integer orderId);
    /**
     * 更新商品投资状态
     * @param productId
     * @param isInvested 0：未投资 1：已投资
     */
    @Modifying
    @Query(value = "update yx_store_product set is_invested=?2,invest_name=?3 where id=?1",nativeQuery = true)
    void updateIsInvested(Integer productId, int isInvested,String investName);
    /**
     * 查询支付超时的商品ID
     * @param investStatus
     * @param payTimeout
     * @return
     */
    @Query(value = "select yxc.product_id as productId,yx.id from yx_invest_order yx inner join yx_invest_order_cart_info yxc on yx.id=yxc.order_id " +
            " where invest_status=?1  and TIMESTAMPDIFF(MINUTE, add_time, NOW())> (select value from yx_system_config  where menu_name=?2)",nativeQuery = true)
    List<Map<String,Integer>> findListGoods(int investStatus, String payTimeout);
    /**
     * 更新订单支付状态
     * @param id
     * @param paid
     */
    @Modifying
    @Query(value = "update yx_store_product set paid=?2 where id=?1",nativeQuery = true)
    void updatePayStatus(Integer id, int paid);

    /**
     * 随机获取数据
     * @param number
     * @return
     */
    @Query(value = "SELECT * FROM yx_invest_info_machine where type=1 ORDER BY RAND() LIMIT ?1",nativeQuery = true)
    List<Map<String,Object>> getMachine(int number);
}